--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';


--CREATE TABLE se_chf_fraisid AS (SELECT * FROM &erp.chf_fraisid);
DELETE FROM se_chf_fraisid;

 INSERT INTO se_chf_fraisid
 (SELECT 'DOUANE' frais_cod,
         (SELECT enreg_id FROM &erp.par_unfab f WHERE f.unfab_cod=uf.id) parentenreg_id,
         'N' enreg_s,
         Trunc(SYSDATE,'year') deb_dat,
         NULL fin_dat,
         fraiscee/10000 frais_coef,
         NULL frais_mt,
         'EUR' devise_cod,
         NULL typunite_cod,
         0 enreg_id
    FROM uv_pfournisseurs  uf
    WHERE fraiscee IS NOT null) ;

 UPDATE se_chf_fraisid tf SET enreg_id=Nvl((select enreg_id FROM &erp.chf_fraisid f WHERE f.frais_cod=tf.frais_cod AND f.parentenreg_id=tf.parentenreg_id AND f.deb_dat=tf.deb_dat),0) ;
 UPDATE se_chf_fraisid tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('enreg_id','CHF_FRAISID','O') ;

 commit;


SELECT &outils.stragg(source) source,frais_cod, parentenreg_id, enreg_s, deb_dat, fin_dat, frais_coef, frais_mt, devise_cod, typunite_cod, enreg_id
FROM (
  SELECT 'uv' source ,g.* FROM se_chf_fraisid g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.chf_fraisid g where frais_cod='DOUANE'
  )
GROUP BY  frais_cod, parentenreg_id, enreg_s, deb_dat, fin_dat, frais_coef, frais_mt, devise_cod, typunite_cod, enreg_id
HAVING Count(1)!=2
ORDER BY frais_cod,parentenreg_id;



